/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db.JDBC;

import core.Program;
import core.Course;
import core.School;
import core.User;
import db.interfaces.ProgramDAO;
import java.sql.Connection;
import java.util.Collection;
/**
 *
 * @author Anton
 * A class for fetching data from the schhol database.
 */
public class ProgramJdbcDAO extends BaseJdbcDAO implements ProgramDAO
{
    /**
     * 
     * @param con
     */
    public ProgramJdbcDAO(Connection con)
    {
        super(con);
    }
    /**
     * 
     * @param id
     * @return
     */
    @Override
    public Program getProgramById(long id)
    {
        return super.getProgramById(id);
    }

    /**
     * 
     * @param beginsWith
     * @return
     */
    public Collection<Program> searchForProgramsByName(String beginsWith)
    {
        return search(beginsWith, "name");
    }
    /**
     * 
     * @param beginsWith
     * @return
     */
    public Collection<Program> searchForProgramsByProgramId(String beginsWith)
    {
        return search(beginsWith, "programid");
    }
    /**
     * 
     * @param beginsWith
     * @param fieldName
     * @param asc
     * @return
     */
    public Collection<Program> searchForProgramsByParameters(String beginsWith, String fieldName, boolean asc)
    {
        String sql = "";
        String sort = "ASC";
        if(!asc)
            sort = "DESC";
        if(fieldName.equals(""))
            sql = "SELECT * FROM Programs ORDER BY " + fieldName + " " + sort +";";
        else
            sql = "SELECT * FROM Programs WHERE " +
                "id LIKE '" + beginsWith + "%' OR " +
                "name LIKE '" + beginsWith + "%' OR " +
                "programid LIKE '" + beginsWith + "%' ORDER BY " +
                fieldName + " " + sort +";";
        return createProgramCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param beginsWith
     * @param field
     * @return
     */
    private Collection<Program> search(String beginsWith, String field)
    {
        String sql = "SELECT * FROM Programs WHERE " + field + " LIKE '" + beginsWith + "%' ORDER BY " + field + " ASC;";
        return createProgramCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param name
     * @return
     */
    public Collection<Program> getProgramsByName(String name)
    {
        if(name == null)
            return null;
        String sql = "SELECT * FROM Programs WHERE name='" + name + "' ORDER BY programid ASC;";
        return createProgramCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param programId
     * @return
     */
    public Collection<Program> getProgramsByProgramId(String programId)
    {
        if(programId == null)
            return null;
        String sql = "SELECT * FROM Programs WHERE programid='" + programId + "' ORDER BY name ASC;";
        return createProgramCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @return
     */
    public Collection<Program> getAllPrograms()
    {
        String sql = "SELECT * FROM Programs ORDER BY name ASC, programid ASC;";
        return createProgramCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param school
     * @return
     */
    public Collection<Program> getAllProgramsInSchool(School school)
    {
        if(school == null)
            return null;
        String sql = "SELECT * FROM SchoolProgram WHERE school='" + school.getId() + "';";
        return createProgramCollection(createIdCollection(sqlExecuteQuery(sql), "program"));
    }
    /**
     * 
     * @param user
     * @return
     */
    public Collection<Program> getAllProgramsInUser(User user)
    {
        if(user == null)
            return null;
        String sql = "SELECT * FROM UserProgram WHERE user = '" + user.getId() + "';";
        return createProgramCollection(createIdCollection(sqlExecuteQuery(sql), "program"));
    }
    /**
     * 
     * @param program
     * @param course
     * @return
     */
    public boolean addCourseToProgram(Program program, Course course)
    {
        if(program == null || course == null)
            return false;
        String sql = "INSERT INTO ProgramCourse (program, course) " +
                "VALUES ("
                + "'" + program.getId() + "', "
                + "'" + course.getId() + "');";
        return add(sql);
    }
    /**
     * 
     * @param program
     * @param course
     * @return
     */
    public boolean deleteCourseFromProgram(Program program, Course course)
    {
        if(program == null || course == null)
            return false;
        String sql = "DELETE FROM ProgramCourse WHERE program = '" + program.getId() + "' AND course = '" + course.getId() + "';";
        return delete(sql);
    }
    /**
     * 
     * @param program
     * @return
     */
    @Override
    public boolean programIsBound(Program program)
    {
        if(program == null)
            return false;
        return super.programIsBound(program);        
    }
    /**
     * 
     * @param program
     * @return
     */
    public boolean containsProgram(Program program)
    {
        if(program == null)
            return false;
        String sql = "SELECT * FROM Programs WHERE id='" + program.getId() + "';";
        return contains(sql);
    }
    /**
     * 
     * @param program
     * @return
     */
    public boolean addProgram(Program program)
    {
        if(program == null)
            return false;
        String sql = "INSERT INTO Programs (name, programid, description, weblink) " +
                           "VALUES ("
                           + "'" + program.getName() + "', "
                           + "'" + program.getProgramId() + "', "
                           + "'" + program.getDescription() + "', "
                           + "'" + program.getWebLink() + "');";
        boolean b = add(sql);
        program.setId(getLatestAddedId("Programs"));
        return b;
    }
    /**
     * 
     * @param program
     * @return
     */
    public boolean editProgram(Program program)
    {
        if(program == null)
            return false;
        String sql = "UPDATE Programs SET  " +
                "name='" + program.getName() +
                "', description = '" + program.getDescription() +
                "', weblink = '" + program.getWebLink() +
                "', programid = '" + program.getProgramId() +
                "' WHERE id='" + program.getId() + "';";
        return edit(sql);
    }
    /**
     * 
     * @param program
     * @return
     */
    public boolean deleteProgram(Program program)
    {
        if(program == null)
            return false;
        deleteProgramFromAllBindings(program);
        String sql = "DELETE FROM Programs WHERE id = ' " + program.getId() + "';";
        return delete(sql);
    }
    /**
     * 
     * @param program
     */
    private void deleteProgramFromAllBindings(Program program)
    {
        if(program == null)
            return;
        removeProgramfromUsers(program);

        String sql = "DELETE FROM ProgramCourse WHERE program = ' " + program.getId() + "'";
        delete(sql);
    }
}
